from sqlalchemy import create_engine, Column, Integer, String, text, inspect, CursorResult
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

from blog.mydb_util import Article

# 创建数据库引擎
# 请根据实际情况修改数据库连接信息
#engine = create_engine('mysql+mysqlconnector://root:123456@127.0.0.1:3306/django-web')
engine = create_engine('mysql+mysqlconnector://root:Wit2cloud!24@43.138.226.128:3306/ssc-platform')
# 创建会话工厂
Session = sessionmaker(bind=engine)
session = Session()

# 创建基类
Base = declarative_base()



#增删改查
class MySqlDb:
    #查询，Model形式
    @staticmethod
    def get_mysql_session():
        return session

    #删除，sql形式
    @staticmethod
    def delete(sql:str,params:dict=None):
        """
        执行 SQL 删除操作
        :param sql: SQL 删除语句（可使用:param_name形式参数）
        :param params: 参数字典，如{'id':1}
        :return: 受影响的行数
        """
        try:
            with engine.connect() as conn:
                # 执行带参数的删除操作
                result = conn.execute(text(sql), params or {})
                conn.commit()  # 提交事务
                return result.rowcount  # 返回受影响的行数
        except Exception as e:
            print(f"删除操作失败: {str(e)}", e)
            conn.rollback()  # 发生异常时回滚
            return 0


    #更新，sql形式更新
    @staticmethod
    def update(sql:str,params:dict=None):
        """
        执行 SQL 更新操作
        :param sql: SQL 更新语句（可使用:param_name形式参数）
        :param params: 参数字典，如{'id':1,'title':'Python'}
        :return: 受影响的行数
        """
        try:
            with engine.connect() as conn:
                # 执行带参数的更新操作
                result = conn.execute(text(sql), params or {})
                conn.commit()  # 提交事务
                return result.rowcount  # 返回受影响的行数
        except Exception as e:
            print(f"更新操作失败: {str(e)}", e)
            conn.rollback()  # 发生异常时回滚
            return 0

    #新增,Model形式
    @staticmethod
    def insert_by_model(model:Base):
        """
        执行 SQL 插入操作
        :param model: SQLAlchemy模型对象
        :return: 成功返回True，失败返回False
        """
        try:
            session.begin()  # 显式开始事务
            session.add(model)
            session.commit()
            return True
        except Exception as e:
            print(f"模型插入失败: {str(e)}")
            if 'session' in locals() and session:
                session.rollback()  # 回滚事务
            return False
        finally:
            if 'session' in locals() and session:
                session.close()  # 确保会话关闭

    #新增,sql形式
    @staticmethod
    def insert(sql:str,params:dict=None):
        """
        执行 SQL 插入操作
        :param sql: SQL 插入语句（可使用:param_name形式参数）
        :param params: 参数字典，如{'id':1,'title':'Python'}
        :return: 受影响的行数
        """
        try:
            with engine.connect() as conn:
                # 执行带参数的插入操作
                result = conn.execute(text(sql), params or {})
                conn.commit()  # 提交事务
                return result.rowcount  # 返回受影响的行数
        except Exception as e:
            print(f"插入操作失败: {str(e)}", e)
            conn.rollback()  # 发生异常时回滚
            return 0



    #查询1条
    @staticmethod
    def query_one(sql:str,params:dict=None):
        """
        查询1条数据
        :param sql: sql语句
        :param params: 参数
        :return: 返回字典数据对象
        """
        try:
            with engine.connect() as conn:
                # 执行带参数的查询
                result: CursorResult = conn.execute(text(sql), params or {})

                # 获取列名
                columns = [col[0] for col in result.cursor.description]

                # 获取第一条记录
                row = result.fetchone()
                if row is None:
                    return None

                # 转换为字典
                row_dict = {}
                for idx, value in enumerate(row):
                    col_name = columns[idx]
                    # 处理日期/时间类型
                    if hasattr(value, 'isoformat'):
                        row_dict[col_name] = value.isoformat()
                    else:
                        row_dict[col_name] = value
                return row_dict
        except Exception as e:
            print(f"查询执行失败: {str(e)}", e)
            return None


    #查询数据(无分页）
    @staticmethod
    def query(sql:str,params:dict=None):
        """
        查询数据
        :param sql: sql语句
        :param params: 参数
        :return:
        """
        try:
            with engine.connect() as conn:
                # 执行带参数的查询
                result: CursorResult = conn.execute(text(sql), params or {})

                # 获取列名
                columns = [col[0] for col in result.cursor.description]
                json_data = []
                for row in result:
                    row_dict = {}
                    for idx, value in enumerate(row):
                        col_name = columns[idx]
                        # 处理日期/时间类型
                        if hasattr(value, 'isoformat'):
                            row_dict[col_name] = value.isoformat()
                        else:
                            row_dict[col_name] = value
                    json_data.append(row_dict)
                return json_data
        except Exception as e:
            print(f"查询执行失败: {str(e)}", e)
            return []

    #查询数据(有分页）
    def query_by_page(sql:str,params:dict=None,page:int=1,page_size:int=10):
        """
        执行 SQL 查询并返回 JSON 格式数据（支持分页和参数化查询）
        :param sql: SQL 查询语句（可使用:param_name形式参数）
        :param params: 参数字典，如{'id':1,'title':'Python'}
        :param page: 页码，从1开始
        :param page_size: 每页条数，默认5条
        :return: {'total':总条数, 'data':字典列表}
        """
        try:
            with engine.connect() as conn:
                # 先查询总条数
                count_sql = f"SELECT COUNT(1) FROM ({sql}) as t"
                total = conn.execute(text(count_sql), params or {}).scalar()

                # 添加分页条件
                paginated_sql = f"""
                    SELECT * FROM ({sql}) as t
                    LIMIT {page_size} OFFSET {(page-1)*page_size}
                """

                # 执行带参数的查询
                result: CursorResult = conn.execute(text(paginated_sql), params or {})

                # 获取列名
                columns = [col[0] for col in result.cursor.description]

                json_data = []
                for row in result:
                    row_dict = {}
                    for idx, value in enumerate(row):
                        col_name = columns[idx]
                        # 处理日期/时间类型
                        if hasattr(value, 'isoformat'):
                            row_dict[col_name] = value.isoformat()
                        else:
                            row_dict[col_name] = value
                    json_data.append(row_dict)

                return {
                    'total': total,
                    'data': json_data
                }
        except Exception as e:
            print(f"查询执行失败: {str(e)}", e)
            return {'total': 0, 'data': []}


# if __name__ == '__main__':
#     #查询Article表,blog_article
#     # sql = "SELECT * FROM blog_article where id=:id"
#     # result = MySqlDb.query(sql,{'id':1})
#     # print(result)
#     #查询Article表,blog_article
#     sql = "SELECT * FROM blog_article where id=:id"
#     result = MySqlDb.query_one(sql,{'id':1})
#     print("查询1条",result)
#     #通过模型查询数据
#
#     #新增
#     article = Article(title='计算机科学', author='王五', price=300, content='C++是一种通用的编程语言', pub_date='2023-05-03')
#     result = MySqlDb.insert_by_model(article)


